<%@ page language="java" contentType="text/html; charset=utf-8"
    pageEncoding="utf-8"
    import="java.sql.*"
    import="java.util.Calendar"
    %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>新增逾期紀錄</title>
</head>
<body>
<% if(session.getAttribute("access") == "y" ){%>
<jsp:useBean id="mydb" scope="page" class="bean.ConDbBean"/> <%//連結資料庫的bean %>
<jsp:useBean id="sendmail" scope="page" class="bean.SendMail"/><%//寄發mail %>
<jsp:useBean id="member" scope="page" class="myclass.Member"/> <%//取得會員資料 %>
<%
	String date=""; 
	String sql="";
	ResultSet rs;
	ResultSet rs2;
	ResultSet rs3;
    Calendar cal =Calendar.getInstance(); 
	String y1 = ""; //查詢開始年份 
   	String m1 = ""; //查詢開始月份 
   	String d1 = ""; //查詢開始日期 
   	//String mid="";
    y1 = String.valueOf(cal.get(cal.YEAR)); 
    m1=String.valueOf(cal.get(cal.MONTH)+1); 
    d1 =String.valueOf(cal.get(cal.DATE));
    date=y1+"/"+m1+"/"+d1;
    //找尋逾期的借用資料
	sql="select * from BORROW WHERE MUST_RETURN_DATE<'"+date+"'";
	rs=mydb.executeQuery(sql);
	while(rs.next()){
		sql="select * from PLACE WHERE P_ID='"+rs.getString("PF_ID")+"'";
		rs2=mydb.executeQuery(sql);
		//P_STATE='11'表示此借用資料逾期
  		if(rs2.next()){//為場地資料
  			sql="UPDATE PLACE SET P_STATE='11' WHERE P_ID='"+rs.getString("PF_ID")+"'";
  		}else{
  			sql="select * from FACILITY WHERE F_ID='"+rs.getString("PF_ID")+"'";
			rs2=mydb.executeQuery(sql);
  			if(rs2.next()){//為設備資料
  				sql="UPDATE FACILITY SET F_STATE='11' WHERE F_ID='"+rs.getString("PF_ID")+"'";
  			}
  		}
  		mydb.executeUpdate(sql);
  		sql="select * from DELAY WHERE PF_ID='"+rs.getString("PF_ID")+"'";
		rs3=mydb.executeQuery(sql);
		if(!rs3.next()){
			//mid=rs3.getString("M_ID");
			sql="INSERT INTO DELAY(M_ID,PF_ID,MUST_RETURN_DATE,PAY) VALUES('"+rs.getString("M_ID")+"','"+rs.getString("PF_ID")+"','"+rs.getString("MUST_RETURN_DATE")+"','0')";
			mydb.executeInsert(sql);
		
		/*寄信通知會員逾期事項*/

		member.setMember(rs.getString("M_ID"));
		sendmail.get_pto(member.getEmail());//取得會員信箱
		sendmail.setSubject("delay");//郵件標題
		String sqlmail="select * from SYS_SETTING WHERE S_NAME='delay_text'";
  	    rs=mydb.executeQuery(sqlmail);
    	  if(rs.next()){
      		String mag = "";
      		mag = rs.getString(3);
       		mag = mag+"<br/>您有逾期的場地/設備  請盡快歸還!";    
			sendmail.setText(mag);
			sendmail.sendMail();
      	  }
		
		}
	}
	

	
	
response.sendRedirect("../SECURITY/do_checkLevel.jsp");
%>

<%
  }
  else{
  	response.sendRedirect("../index.jsp"); //若沒有登入則回到index.jsp
  } 
  %>
</body>
</html>